package com.tangerine.bd;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import android.database.Cursor;
import com.tangerine.entidades.Historial;

public class DbHandlerHistorial{
	private static DbHandler bd; 
	private static final String TABLE_HISTORIAL = "HISTORIAL";
	private static final String TABLE_POI       = "POI";
	// Table HISTORIAL
	private static final int CAMPO_ID_POI 				= 0;
	private static final int CAMPO_ID_PERFIL			= 1;
	private static final int CAMPO_FECHA_ULT_CONSTULTA 	= 2;

	//Constructor
	public DbHandlerHistorial() {
		bd = DbHandler.getInstance();
	}
	
	public List<Historial> cargarHistorial() throws SQLException {
		List<Historial> list = new ArrayList<Historial>();	

		String selectQuery = "SELECT * FROM " + TABLE_HISTORIAL;
		Cursor cursor = bd.ejecutarConsulta(selectQuery); 
		
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Historial auxHistorial = new Historial();
				auxHistorial.setIdPoi(Integer.parseInt(cursor.getString(CAMPO_ID_POI)));
				auxHistorial.setIdPerfil(Integer.parseInt(cursor.getString(CAMPO_ID_PERFIL)));
				auxHistorial.setFechaUltimoConsultado(cursor.getString(CAMPO_FECHA_ULT_CONSTULTA));
				list.add(auxHistorial);
			} while (cursor.moveToNext());
		}
		
		cursor.close();

		return list;
	}
	
	//return 0 - Table HISTORIAL empty
	public int existeHistorial(Historial auxHistorial) throws SQLException {		
		int iCantReg = 0;

		String selectQuery = "SELECT COUNT(1) FROM " + TABLE_HISTORIAL + " H, " + TABLE_POI + " P WHERE H.ID_POI = P.ID_POI AND P.ID_POI = " 
				+ Integer.toString(auxHistorial.getIdPoi());
		
		Cursor cursor = bd.ejecutarConsulta(selectQuery); 

		if (cursor.moveToFirst()) {
			do {
				iCantReg = Integer.parseInt(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		
		cursor.close();

		if(iCantReg == 0)
			return 0;
		else
			return 1;			
	}

	public void insertarHistorial(Historial auxHistorial) throws SQLException {	
		String insertRegister = "INSERT INTO " + TABLE_HISTORIAL + " (ID_POI, ID_PERFIL, FECHA_ULT_CONSULTA) VALUES(" +
				Integer.toString(auxHistorial.getIdPoi()) + ", " + Integer.toString(auxHistorial.getIdPerfil()) + ", " + "DATETIME('now'))";

		bd.ejecutarInsert(insertRegister);
	}
	
	public void eliminarHistorial() throws SQLException {
		String deleteTable = "DELETE FROM " + TABLE_HISTORIAL + " WHERE FECHA_ULT_CONSULTA < (SELECT DATETIME('now','-7 day'))";
		
		bd.ejecutarDelete(deleteTable);
	}
	
}